<?php
defined('BASEPATH') OR exit('No direct script access allowed');

//风资损益表管理
class Fenzi extends CommonController {

	function __construct(){
		
		parent::__construct();
		$this->load->model('Accounting/accountingModel','CurModel');
		
	}
	
	
	//start
	/**
	* @name 获取列表
	* @return 成功返回1
	*/
	//end
	function query(){
		
		if(!$this->check_priv('report_fengzi|report_fengzi_1')){
			$this->err_out(LANG_NOPRIV);
		}
		
		$user_id = $this->user_id;
		$filter = $this->input->post('f',null,array());
		$bz = $this->input->post('bz',null,'人民币');
		
		
		if(empty($filter['date'])){
			$this->err_out('请选择年份');
		}
		
		
		
		
		$curinfo = null;
		
		$this->load->model('User/CurrencyModel','CurrencyModel');
		$curinfo = $this->CurrencyModel->bz_get_info($bz);
		if(!$curinfo){
			$curinfo = array(
				'name'=>'人民币',
				'exchange_rate'=>1,
			);
		}
		
		$columns = $this->get_columns($filter['date'],$curinfo['name']);
		
		$this->load->dmodel('subject_type','subject_type');
		$this->load->model('User/SubjectModel','SubjectModel');
		
		
		$ls_list = $this->subject_type->getAll(array(),'name,id',0,0,'sort');
		
		$list = array();
		$farr = array('营业收入类','营业成本类','运营开支类');
		foreach($ls_list as $val){
			if(in_array($val['name'],$farr)){
				$list[] = $val;
			}
		}
		
		
		
		foreach($list as $key=>$val){
			
			$res = $this->get_subject($filter,$columns,$val['id'],$curinfo['exchange_rate'],0);
			$arr = $res['list'];
			
			$date_totals = $res['totals'];
			
			$ls_data = array(
				'name'=>'合计',
			);
			foreach($date_totals as$k=>$v){
				$ls_data[$k] = $v;
			}
			
			$arr[] = $ls_data;
			
			
			$list[$key]['list'] = $arr;
			
			
		}

		$yg_list = array();
		
		$shouru  = end($list[0]['list']);
		$kaizhi  = end($list[2]['list']);
		$chengben  = end($list[1]['list']);
		
		//计算净盈利/亏损
		$lirun = array(
			'name'=>'净盈利/亏损'
		);
		
		foreach($columns as $val){
			if($val['key']=='name'){
				continue;
			}
			$ls_shouru = isset($shouru[$val['key']])?$shouru[$val['key']]:0;
			$ls_kaizhi = isset($kaizhi[$val['key']])?$kaizhi[$val['key']]:0;
			
			$ls_num =  $ls_shouru - $ls_kaizhi;
			
			$lirun[$val['key']] = toFloat($ls_num);
		}
		$yg_list[] = $lirun;
		
		
		//计算毛利率
		$ls_data = array(
			'name'=>'毛利率'
		);
		foreach($columns as $val){
			if($val['key']=='name'){
				continue;
			}
			$ls_lirun = isset($lirun[$val['key']])?$lirun[$val['key']]:0;
			$ls_shouru = isset($shouru[$val['key']])?$shouru[$val['key']]:0;
			
			if($ls_shouru==0){
				$ls_num =  0;
			}
			else{
				$ls_num =  $ls_lirun/ $ls_shouru * 100; //毛利率=毛利润/营业收入×100%
			}
			
			$ls_data[$val['key']] = toFloat($ls_num).'%';
		}
		
		$yg_list[] = $ls_data;
		
		//计算纯利率
		
		$ls_data = array(
			'name'=>'纯利率'
		);
		foreach($columns as $val){
			if($val['key']=='name'){
				continue;
			}
			$ls_lirun = isset($lirun[$val['key']])?$lirun[$val['key']]:0;
			$ls_chengben = isset($chengben[$val['key']])?$chengben[$val['key']]:0;
			
			if($ls_chengben==0){
				$ls_num =  0;
			}
			else{
				$ls_num =  $ls_lirun/ $ls_chengben * 100; //净利率=利润/成本×100%
			}
			
			$ls_data[$val['key']] = toFloat($ls_num).'%';
		}
		
		$yg_list[] = $ls_data;
		
		
		$list[] = array(
			'name'=>'盈亏',
			'list'=>$yg_list
		);

		
		
		
		
		$data = array(
			'columns'=>$columns,
			'data'=>$list,
			'currency'=>$curinfo
		);
		
		$this->data_out($data);
	}
	
	function get_subject($filter,$columns,$typeid,$exchange_rate=1,$parent_id=0){
		
		$month_num = 12;
		
		
		$sublist = $this->SubjectModel->getAll(array('type'=>$typeid,'parent_id'=>$parent_id));
		
		if(!$sublist){
			return false;
		}
		$arr = array();
		
		
		$date_totals = array(
			
		);
		
		foreach($sublist as $k=>$v){
			
			$ls_data = array();
			$ls_data['name'] = $v['name'];
			$ls_data['code'] = $v['code'];
			$ls_data['is_open'] = 0;
			$total = 0;
			$prev_year = 0;
			
			
			
			
			foreach($columns as $item){
				if(!isset($item['value'])){
					continue;
				}
				$warr = array(
					'subject'=>$v['code'].$v['name'],
				);
				
				foreach($filter as $key=>$val){
					$warr[$key]=$val;
				}
				
				
				$sub_total = $this->get_sum_total($item['value'],$warr,$v['name']);
				$sub_total = toFloat($sub_total * $exchange_rate);
				
				
				
				$ls_data[$item['key']] = toFloat($sub_total);
				$total += $sub_total;
				
				/*
				if(isset($date_totals[$item['key']])){
					$date_totals[$item['key']] += $sub_total;
				}
				else{
					$date_totals[$item['key']] = $sub_total;
				}
				*/
				
				
				//上年计算	
				$ls_date = date('Y-m',strtotime("- 1year",strtotime($item['value'].'-01')));
				
				$ls_total = $this->get_sum_total($ls_date,$warr,$v['name']);
				$ls_total = toFloat($ls_total * $exchange_rate);
				
				
				$prev_year += $ls_total;
			}
			
			$ls_data['total'] = toFloat($total);
			$ls_data['equally'] = toFloat($total/$month_num);
			
			//上年度同期
			$ls_data['prev_year'] = $prev_year;
			//同期比较
			$prev_year_percent = 0;
			if($ls_data['total']>0 && $ls_data['prev_year']>0){
				$prev_year_percent = toFloat(($ls_data['total']-$ls_data['prev_year'])/ABS($ls_data['prev_year']));
			}
			
			$ls_data['prev_year_percent'] = $prev_year_percent;
			
			
			
			
			
			/*
			$date_totals['total'] += $ls_data['total'];
			$date_totals['equally'] += $ls_data['equally'];
			$date_totals['prev_year'] += $ls_data['prev_year'];
			$date_totals['prev_year_percent'] += $ls_data['prev_year_percent'];
			*/
			
			
			$childs = $this->get_subject($filter,$columns,$typeid,$exchange_rate,$v['id']);
			if($childs){
				$ls_data['childs'] = $childs;
				foreach($columns as $col){
					if($col['key']=='name'){
						continue;
					}
					
					$ls_data[$col['key']] = 0;
					foreach($childs['list'] as $item){
						$ls_data[$col['key']]+= toFloat($item[$col['key']]);
					}
				}
				
			}
			
			$arr[] = $ls_data;
		}
		
		foreach($arr as $val){
			
			foreach($columns as $col){
				if($col['key']=='name'){
					continue;
				}
				$total = isset($val[$col['key']])?floatval($val[$col['key']]):0;
				
				if(!isset($date_totals[$col['key']])){
					$date_totals[$col['key']] = $total;
				}
				else{
					$date_totals[$col['key']] += $total;
				}
			}
		}
		$date_totals['name'] = '合计';
		
		
		return array('list'=>$arr,'totals'=>$date_totals);
	}
	function get_sum_total($date_month,$arr,$subname=''){

		
		if($this->CurModel->has_data(array('date_month'=>$date_month))<=0){
			return 0;
		}
		
		if($this->CurModel->has_data(array('subject'=>$arr['subject']))<=0){
			return 0;
		}
		
		$arr['date_month'] = $date_month;
		
		//计算现金账目
		$sub_total = $this->CurModel->get_totals($arr);
		
		return $sub_total;
	}
	
	function get_columns($year,$cur_name){
		
		
		$month_num = 12;
		$columns = array();
		$columns[] = array(
				'title'=>"({$cur_name})",
				'key'=>'name'
			);
		
		$ls_date = $year.'-01-01';
		for($i=0;$i<$month_num;$i++){
			
			$key = 'm'.($i+1);
			$title =($i+1).'月';
			$value = date('Y-m',strtotime("+{$i} month",strtotime($ls_date)));
			
			$columns[] = array(
				'value'=>$value,
				'key'=>$key,
				'title'=>$title
			);
		}
		$columns[] = array(
				'title'=>'总计',
				'key'=>'total',
				'width'=>80
			);
		$columns[] = array(
				'title'=>'月平均',
				'key'=>'equally',
				'width'=>80
			);
		$columns[] = array(
				'title'=>'上年度同期',
				'key'=>'prev_year',
				'width'=>100
			);
		$columns[] = array(
				'title'=>'同期比较',
				'key'=>'prev_year_percent',
				'width'=>100
			);
		return $columns;
	}
	
	//导出数据
	function export_data(){
		
		if(!$this->check_priv('report_fengzi|report_fengzi_1')){
			$this->err_out(LANG_NOPRIV);
		}
		
		
		$user_id = $this->user_id;
		$filter = $this->input->post('f',null,array());
		$bz = $this->input->post('bz',null,'人民币');
		
		
		if(empty($filter['date'])){
			$this->err_out('请选择年份');
		}
		
		
		$curinfo = null;
		
		$this->load->model('User/CurrencyModel','CurrencyModel');
		$curinfo = $this->CurrencyModel->bz_get_info($bz);
		if(!$curinfo){
			$curinfo = array(
				'name'=>'人民币',
				'exchange_rate'=>1,
			);
		}
		
		
		$columns = $this->get_columns($filter['date'],$curinfo['name']);
		
		
		
		
		$this->load->dmodel('subject_type','subject_type');
		$this->load->model('User/SubjectModel','SubjectModel');
		
		
		$ls_list = $this->subject_type->getAll(array(),'name,id',0,0,'sort');
		
		$list = array();
		$farr = array('营业收入类','营业成本类','运营开支类');
		foreach($ls_list as $val){
			if(in_array($val['name'],$farr)){
				$list[] = $val;
			}
		}
		
		
		
		foreach($list as $key=>$val){
			
			$res = $this->get_subject($filter,$columns,$val['id'],$curinfo['exchange_rate'],0);
			$ls_arr = $res['list'];
			
			$date_totals = $res['totals'];
			
			$ls_data = array(
				'name'=>'合计',
			);
			foreach($date_totals as$k=>$v){
				$ls_data[$k] = $v;
			}
			
			$ls_arr[] = $ls_data;
			
			
			$list[$key]['list'] = $ls_arr;
			
			
		}

		$yg_list = array();
		
		$shouru  = end($list[0]['list']);
		$kaizhi  = end($list[2]['list']);
		$chengben  = end($list[1]['list']);
		
		//计算净盈利/亏损
		$lirun = array(
			'name'=>'净盈利/亏损'
		);
		
		foreach($columns as $val){
			if($val['key']=='name'){
				continue;
			}
			$ls_shouru = isset($shouru[$val['key']])?$shouru[$val['key']]:0;
			$ls_kaizhi = isset($kaizhi[$val['key']])?$kaizhi[$val['key']]:0;
			
			$ls_num =  $ls_shouru - $ls_kaizhi;
			
			$lirun[$val['key']] = toFloat($ls_num);
		}
		$yg_list[] = $lirun;
		
		
		//计算毛利率
		$ls_data = array(
			'name'=>'毛利率'
		);
		foreach($columns as $val){
			if($val['key']=='name'){
				continue;
			}
			$ls_lirun = isset($lirun[$val['key']])?$lirun[$val['key']]:0;
			$ls_shouru = isset($shouru[$val['key']])?$shouru[$val['key']]:0;
			
			if($ls_shouru==0){
				$ls_num =  0;
			}
			else{
				$ls_num =  $ls_lirun/ $ls_shouru * 100; //毛利率=毛利润/营业收入×100%
			}
			
			$ls_data[$val['key']] = toFloat($ls_num).'%';
		}
		
		$yg_list[] = $ls_data;
		
		//计算纯利率
		
		$ls_data = array(
			'name'=>'纯利率'
		);
		foreach($columns as $val){
			if($val['key']=='name'){
				continue;
			}
			$ls_lirun = isset($lirun[$val['key']])?$lirun[$val['key']]:0;
			$ls_chengben = isset($chengben[$val['key']])?$chengben[$val['key']]:0;
			
			if($ls_chengben==0){
				$ls_num =  0;
			}
			else{
				$ls_num =  $ls_lirun/ $ls_chengben * 100; //净利率=利润/成本×100%
			}
			
			$ls_data[$val['key']] = toFloat($ls_num).'%';
		}
		
		$yg_list[] = $ls_data;
		
		
		$list[] = array(
			'name'=>'盈亏',
			'list'=>$yg_list
		);
		
		$arr = array();
		$merges = array();
		
		$line = 1;
		foreach($list as $key=>$val){
			$arr['A'.$line] = $val['name'];
			
			$ls_col = $this->get_char(count($columns)-1,'A');
			$merges[] = "A{$line}:{$ls_col}{$line}";
			
			$line++;
			foreach($columns as $k=>$col){
				$ls_col = $this->get_char($k,'A');
				$arr[$ls_col.$line] = $col['title'];
			}
			$line++;
			$ls_list = $val['list'];
			foreach($ls_list as $v){
				foreach($columns as $k=>$col){
					$ls_col = $this->get_char($k,'A');
					
					if($col['key']=='name'){
						$ls_val = (isset($v['code'])?$v['code']:'').$v['name'];
					}
					else{
						$ls_val = $v[$col['key']];
					}
					
					$arr[$ls_col.$line] = $ls_val;
				}
				$line++;
				
				if(isset($v['childs']) && is_array($v['childs'])){
					$childs = $v['childs']['list'];
					
					foreach($childs as $item){
						foreach($columns as $k=>$col){
							$ls_col = $this->get_char($k,'A');
							if($col['key']=='name'){
								$ls_val = (isset($item['code'])?$item['code']:'').$item['name'];
							}
							else{
								$ls_val = $item[$col['key']];
							}
							$arr[$ls_col.$line] = $ls_val;
						}
						$line++;
					}
				}
				
			}
		}
		
		
		
		$result = $this->Excel_write($arr,$merges);

		if(!$result){
			$this->err_out('生成Excel文件失败');
		}
		
		$url = base_url($result);
		
		$this->data_out($url);
	}
	
	function get_char($num,$pos='A'){
		if($num==0){
			return $pos;
		}

		for($i=0;$i<$num;$i++){
			$pos++;
		}
		return $pos;
	}
	
	function Excel_write($data,$merges=array()){
		$this->load->library('PHPExcel');
		
		// 创建Excel文件对象
		$objPHPExcel = new PHPExcel();
		// 设置文档信息，这个文档信息windows系统可以右键文件属性查看
		$objPHPExcel->getProperties()->setCreator("作者lalala")
			->setLastModifiedBy("最后更改者")
			->setTitle("文档标题")
			->setSubject("文档主题")
			->setDescription("文档的描述信息")
			->setKeywords("设置文档关键词")
			->setCategory("设置文档的分类");
		
		$objPHPExcel->setActiveSheetIndex(0);

		
									
		
		$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		foreach($data as $key=>$val){
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($key,$val);
		}
		
		foreach($merges as $key=>$val){
			$objPHPExcel->setActiveSheetIndex(0)->mergeCells($val);
		}
		
		//$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//$objPHPExcel ->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(0))->setAutoSize(true); 
		$objPHPExcel ->getActiveSheet()->getColumnDimension("A")->setWidth(30); 
		//$objPHPExcel->getActiveSheet()->setTitle('第一个sheet');
		
		// 设置第一个sheet为工作的sheet
		

		// 保存Excel 2007格式文件，保存路径为当前路径，名字为export.xlsx
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$time = time();
		$dir  =  'upload/res/upload/other/'.date('Ymd');
		//$dir = '';
		if(!is_dir(FCPATH . $dir)){
			@mkdir(FCPATH . $dir);
		}
		
		$file_name = $time.'_'.rand(10000,99999).'.xlsx';
		$objWriter->save(FCPATH . $dir .'/' .$file_name);
		
		return $dir .'/' .$file_name;
	}
	
}
